package com.ugoodtech.mdcc.core.repository;

import com.ugoodtech.mdcc.core.domain.Station;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface StationRepository extends PagingAndSortingRepository<Station, Long>
        , QueryDslPredicateExecutor<Station> {

    @Query("from Station where user_id=:userId")
    Station findByUserId(@Param("userId") Long userId);

    @Query("from Station where stationNum=:stationNum and deleted=false and enabled=true")
    Station findByStationNum(@Param("stationNum") String stationNum);

    @Query(value = "select a.name,count(b.id) from question_answer_options a left join stations b on b.brand=a.name  where a.question_id=8 group by a.name", nativeQuery = true)
    List<Object[]> statBrandByStation();

    @Query(value = "select a.name,count(b.id) from question_answer_options a left join stations b on b.province=a.name  where a.question_id=1 group by a.name", nativeQuery = true)
    List<Object[]> statProvinceByStation();

    @Query(value = "select a.name,count(b.id) from question_answer_options a left join stations b on b.category=a.name  where a.question_id=7 group by a.name", nativeQuery = true)
    List<Object[]> statCategory();

    @Query("select count(id) from Station where id=:id and brand=:brand")
    Object countBrand(@Param("id") Long id, @Param("brand") String brand);

    @Query("select count(id) from Station where id=:id and category=:category")
    Object countType(@Param("id") Long id, @Param("category") String category);

    @Query("from Station where name=:name and deleted=false and enabled=true")
    Station selectByName(@Param("name") String name);

    @Query("from Station where name=:name ")
    Station selectByName_check(@Param("name") String name);

    @Query("from Station where stationNum=:stationNum and deleted=false and enabled=true")
    List<Station> findByStationNumList(@Param("stationNum") String stationNum);

    @Query("from Station where id=:id and checkStation=false and deleted=false and enabled=true")
    Station findUnCheckStation(@Param("id") Long stationId);

    @Query("from Station where name=:stationName and deleted=false and enabled=true")
    List<Station> findByStationNameList(@Param("stationName") String stationName);

}
